# encoding: utf-8

from PyQt5.Qt import *
from widget.my_window import MyWindow
from widget.db_init.sqlUtils.sqlserverUtils import SqlServerOp
from widget.daoru_page.source_init_page.add_db_init_window import DbInitWindow
from widget.info_box import InfoBox
from widget.db_init.peewee import testModel229_dp
from widget.db_init.configuration import sql_configuration as LM
from widget.db_init.sqlUtils.mysqlUtils import MySqlOp
import _thread
import time
import configparser
import xlrd
import xlwt


class SqlServerInitPage(MyWindow):
    '''
    sqlserver配置页面（导入页面的子页面）
    '''
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.setAttribute(Qt.WA_StyledBackground, False)
        # 窗口置顶
        self.setWindowModality(Qt.ApplicationModal)
        # self.setWindowFlag(Qt.WindowStaysOnTopHint)
        # 父窗口实例对象
        self.my_parent_window = kwargs['my_parent']
        # 窗口标题
        self.label_title = QLabel(self)
        self.label_title.resize(230, 40)
        self.label_title.setText('Sql-server 来源配置')
        self.label_title.move(10, 5)

        # sqlserver连接
        self.sop = None
        # 加载基本选项卡
        self.setup_ui()
        # 加载表格
        self.init_table()
        # 表名列表
        self.table_name_list = []
        # 加载常规按钮（确定取消）
        self.init_common_btn()

        self.field_common_list = []  # 普通字段
        self.field_pf_list = []  # 主外键字段（主外键id和表名）

    def setup_ui(self):
        '''
        加载三个选择框，用于定位数据表
        :return:
        '''
        # 输入服务器label -------------------------------------------------
        self.server_label = QLabel(self)
        self.server_label.setText('服务器：')
        self.server_label.setFixedWidth(100)
        self.server_label.move(50, 60)
        # input
        self.server_input = QLineEdit(self)
        # self.server_input.setText('192.168.0.6')
        # self.server_input.setText('127.0.0.1')
        # self.server_input.setText('192.168.0.193')
        # self.server_input.setText('192.168.0.56')
        self.server_input.setFixedWidth(120)
        self.server_input.move(120, 57)

        # 输入用户名label -------------------------------------------------
        self.username_label = QLabel(self)
        self.username_label.setText('用户名：')
        self.username_label.setFixedWidth(100)
        self.username_label.move(50, 110)
        # input
        self.username_input = QLineEdit(self)
        # self.username_input.setText('sa')
        self.username_input.setFixedWidth(120)
        self.username_input.move(120, 104)

        # 输入数据库名称label -------------------------------------------------
        self.db_name_label = QLabel(self)
        self.db_name_label.setText('数据库名：')
        self.db_name_label.setFixedWidth(100)
        self.db_name_label.move(470, 60)
        # input
        self.db_name_input = QLineEdit(self)
        # self.db_name_input.setText('standarddb')
        # self.db_name_input.setText('cjg2cxstar')
        # self.db_name_input.setText('sckj2cxstarTP')
        self.db_name_input.setFixedWidth(120)
        self.db_name_input.move(550, 57)

        # 输入端口号label -------------------------------------------------
        self.port_label = QLabel(self)
        self.port_label.setText('端口号：')
        self.port_label.setFixedWidth(100)
        self.port_label.move(260, 60)
        # input
        self.port_input = QLineEdit(self)
        # self.port_input.setText('1433')
        self.port_input.setFixedWidth(120)
        self.port_input.move(330, 57)

        # 输入密码label -------------------------------------------------
        self.password_label = QLabel(self)
        self.password_label.setText('密码：')
        self.password_label.setFixedWidth(100)
        self.password_label.move(260, 110)
        # input
        self.password_input = QLineEdit(self)
        # self.password_input.setText('cxstar2014')
        # self.password_input.setText('cxstar@2014')
        # self.password_input.setText('cxstar2012')
        self.password_input.setFixedWidth(120)
        self.password_input.move(330, 104)

        # 表格选择label -------------------------------------------------
        self.choice_table_label = QLabel(self)
        self.choice_table_label.setText('选择视图：')
        self.choice_table_label.setFixedWidth(100)
        self.choice_table_label.move(50, 160)
        # 表格选择comb
        self.choice_table_comb = QComboBox(self)
        self.choice_table_comb.setView(QListView())  # 通过setView和全局qss设置行高
        self.choice_table_comb.setEnabled(False)
        self.choice_table_comb.setFixedWidth(290)
        self.choice_table_comb.move(150, 156)

        # 测试连接btn -------------------------------------------------
        self.test_btn = QPushButton(self)
        self.test_btn.setText('测试连接')
        self.test_btn.move(710, 55)
        self.test_btn.resize(130, 75)
        self.test_btn.clicked.connect(self.connect_sqlserver)

        # 选择已有库
        self.test_btn = QPushButton(self)
        self.test_btn.setText('选择已有数据库')
        self.test_btn.move(520, 97)
        self.test_btn.resize(150, 30)
        self.test_btn.clicked.connect(self.open_sqlerver_db_init_window)

        self.init_db_conf()

    def get_init_file_cf(self):
        cf = configparser.ConfigParser()
        print(qApp.cx_project_root + r'/init_file/init/sqlserver_db_init.ini')
        cf.read(qApp.cx_project_root + r'/init_file/init/sqlserver_db_init.ini')
        return cf

    def init_write(self, cf):
        cf.write(open(qApp.cx_project_root + r'/init_file/init/sqlserver_db_init.ini', "w"))

    def init_db_conf(self):
        '''
        加载上次的数据库连接配置
        :return:
        '''
        cf = self.get_init_file_cf()
        cur_choice = cf.getint("current_choice", "num")
        if cur_choice == -1:
            return
        print('cf.getint("current_choice", "num"): {0}'.format(cf.getint("current_choice", "num")))
        # 初始化类，打开配置文件
        secs = cf.sections()
        # print(secs)
        if not secs or cur_choice + 2 > len(secs):
            return
        secs.remove('current_choice')
        sec = secs[cur_choice]
        opts = cf.options(sec)
        for k, opt in enumerate(opts):
            value = cf.get(sec, opt)
            print('opt: {0}'.format(value))
            if opt == 'server_name':
                self.server_input.setText(value)
            if opt == 'port':
                self.port_input.setText(value)
            if opt == 'db_name':
                self.db_name_input.setText(value)
            if opt == 'username':
                self.username_input.setText(value)
            if opt == 'password':
                self.password_input.setText(value)

    def init_table(self):
        '''
        加载数据表
        :return:
        '''
        self.table1 = QTableWidget(self)
        self.table1.resize(800, 270)
        self.table1.move(50, 200)
        # 设置表格的列数为2列
        self.table1.setColumnCount(2)
        # self.table1.setVerticalHeaderLabels(["第一列", "第二列"])
        # 设置表格每列的宽度
        self.table1.setColumnWidth(0, 330)
        self.table1.setColumnWidth(1, 330)
        # self.table1.setColumnWidth(2, 80)
        # print(field_list)
        self.table1.setHorizontalHeaderLabels(["字段名称", "数据类型"])

        # 刷新表格
        # self.refresh()
        # 表格监听表，如果表选择框改变，那么刷新表格
        self.choice_table_comb.currentTextChanged.connect(self.refresh)

    # 刷新表格
    def refresh(self, table_name):
        '''
        刷新表格的方法，此方法被绑定到 combo3上，combo3的值改变就会刷新表格，
        同时 combo3 绑定的 combo2， combo2绑定的 combo1，所以实际上形成了三级联动，
        也就是说 改变 服务器（combo1）、数据库（combo2）或者 表（combo3）中任意一个选框的的值，都会进行表格的刷新
        :return:
        '''
        print(table_name)
        if not self.sop:
            return
        # 查询当前表下的
        field_list = self.sop.select(
            """select COLUMN_NAME,DATA_TYPE from information_schema.COLUMNS where table_name = '{0}'""".format(
                table_name))

        # 设置表格行数为查询数据的长度
        self.field_common_list = []   # 普通字段
        self.field_pf_list = []       # 主外键字段（主外键id和表名）
        for field in field_list:
            # print(type(field), field)
            if 'p_ruid' == field[0] or 'f_ruid' == field[0]:
                self.field_pf_list.append(field)
            else:
                self.field_common_list.append(field)

        self.table1.setRowCount(len(self.field_common_list))
        # self.table1.setRowHeight(0, 0)

        # 设置表格每列的宽度
        self.table1.setColumnWidth(0, 330)
        self.table1.setColumnWidth(1, 330)
        # self.table1.setColumnWidth(2, 80)
        # print(field_list)
        # 填充字段
        self.table1.clear()
        self.table1.setHorizontalHeaderLabels(["字段名称", "数据类型"])
        # self.table1.setHorizontalHeaderLabels(["字段名称", "数据类型", "字段说明", "是否导入"])
        for i, field in enumerate(self.field_common_list):
            field_name = field[0]
            field_type = field[1]
            self.table1.setItem(i, 0, QTableWidgetItem(field_name))
            self.table1.setItem(i, 1, QTableWidgetItem(field_type))


        # # 获取选择框中的表名
        # self.table_name = self.combo3.currentText()
        # if self.server_name and self.db_name:
        #     mop = MySqlOp(host=self.server_name, db_name=self.db_name)
        #     # 查询 对应数据库下对应表的 字段名，字段类型，字段说明
        #     field_list = mop.select(
        #         """select COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT from information_schema.COLUMNS where table_name = '{0}' and table_schema = '{1}'""".format(
        #             self.table_name, self.db_name))
        #     # 第一条是主键id，该字段自动生成不用导入
        #     field_list = field_list[1:]
        #     # 设置表格行数为查询数据的长度
        #     self.table1.setRowCount(len(field_list))
        #     # self.table1.setRowHeight(0, 0)
        #
        #     # 设置表格每列的宽度
        #     self.table1.setColumnWidth(0, 220)
        #     self.table1.setColumnWidth(1, 220)
        #     self.table1.setColumnWidth(2, 220)
        #     self.table1.setColumnWidth(3, 95)
        #     # print(field_list)
        #     # 填充字段
        #     self.table1.clear()
        #     self.table1.setHorizontalHeaderLabels(["字段名称", "数据类型", "字段说明", "是否导入"])
        #
        #     for i, field in enumerate(field_list):
        #         for k, f_type in enumerate(field):
        #             self.table1.setItem(i, k, QTableWidgetItem(f_type))
        #         self.table1.setCellWidget(i, 3, QCheckBox())
        #         self.table1.cellWidget(i, 3).setStyleSheet("margin-left: 40px;")

    def connect_sqlserver(self):
        '''
        连接 sqlserver方法，连接成功就查询并返回所有表名，否则返回报错信息
        :return:
        '''
        host = self.server_input.text()
        port = self.port_input.text()
        db_name = self.db_name_input.text()
        user = self.username_input.text()
        passwd = self.password_input.text()
        qApp.cx_mssl_host = host
        qApp.cx_mssl_port = port
        qApp.cx_mssl_db_name = db_name
        qApp.cx_mssl_user = user
        qApp.cx_mssl_passwd = passwd
        if host and port and db_name and user and passwd:
            try:
                print(host, port, db_name, user, passwd)
                self.sop = SqlServerOp(host, port, db_name, user, passwd)
                # 查询当前数据库中的表名
                # sql = """select name from SYSOBJECTS  WHERE XTYPE = 'U' OR XTYPE='V'"""
                # sql = """select name from sys.tables"""
                sql = """select name from SYSOBJECTS  WHERE XTYPE='V' order by name"""
                table_name_list = self.sop.select(sql)

                # #self.conn=pymssql.connect(database=database)
                # sql="use "+database+" SELECT name FROM SYSOBJECTS  WHERE XTYPE = 'U' OR XTYPE='V'"
                # self.cur.execute(sql)
                # table = str(self.cur.fetchall()).split("),")
                # # self.datasourcechild.comboBox_table.clear()
                # for d in table:
                #     self.datasourcechild.comboBox_table.addItem(
                #         d.replace("(", "").replace(",", "").replace(")", "").replace("\'", "").replace(" ", "").replace("[","").replace("]",""))
                #
                #

                self.choice_table_comb.clear()
                # for table_name in table_name_list:
                #     if table_name[0].starts_with(('基础表',)):

                self.choice_table_comb.addItems([table_name[0] for table_name in table_name_list])
                self.choice_table_comb.setEnabled(True)

                # 连接数据库成功，保存连接的配置信息到 sqlserver_db_init.ini 配置文件
                cf = self.get_init_file_cf()
                db_key = '{0}_{1}_{2}'.format(host, port, db_name)

                # 如果当前配置已存在，就删除当前配置
                secs = cf.sections()
                if not db_key in secs:
                    cf.add_section(db_key)
                # 插入新配置
                cf.set(db_key, "server_name", host)
                cf.set(db_key, "port", port)
                cf.set(db_key, "db_name", db_name)
                cf.set(db_key, "username", user)
                cf.set(db_key, "password", passwd)
                self.init_write(cf)
                # 当前选择索引切换到当前数据库的索引
                secs = cf.sections()
                secs.remove('current_choice')   # 取出定制选项（非数据库配置）
                for i, sec in enumerate(secs):
                    if sec == db_key:
                        cf.set("current_choice", "num", str(i))
                        self.init_write(cf)

            except Exception as e:
                print("错误信息{0}".format(e))
                # 消息盒子，显示1秒 start -------------------------------------------
                self.info_box = InfoBox("错误信息{0}".format(e))
                # self.info_box.setWindowFlag(Qt.WindowStaysOnTopHint)
                self.info_box.show()

        else:
            print('连接信息不完整')
            self.info_box = InfoBox('连接信息不完整')
            # self.info_box.setWindowFlag(Qt.WindowStaysOnTopHint)
            self.info_box.show()
            self.choice_table_comb.setEnabled(False)


    def init_common_btn(self):
        '''
        底部的 确定 和 取消 按钮
        :return:
        '''
        # 确定按钮
        self.btn2 = QPushButton(self)
        self.btn2.setText("确定")
        self.btn2.move(620, 490)
        def field_get():
            # 消息盒子，显示1秒 start -------------------------------------------
            if self.table1.rowCount() == 0:
                self.info_box = InfoBox('请先测试连接')

                self.info_box.show()
                def show_1s(info_box):
                    '''
                    提示框展示一秒后销毁
                    :param info_box:
                    :return:
                    '''
                    time.sleep(1)
                    info_box.close()
                _thread.start_new_thread(show_1s, (self.info_box,))
                return
            # 消息盒子，显示1秒 end --------------------------------------------

            # # 消息盒子，显示1秒 start -------------------------------------------
            # check_flag = False
            # for i in range(self.table1.rowCount()):
            #     if self.table1.cellWidget(i, 2).isChecked():
            #         check_flag = True
            # if not check_flag:
            #     self.info_box = InfoBox('请选择主键')
            #     self.info_box.show()
            #     def show_1s(info_box):
            #         '''
            #         提示框展示一秒后销毁
            #         :param info_box:
            #         :return:
            #         '''
            #         time.sleep(1)
            #         info_box.close()
            #
            #     _thread.start_new_thread(show_1s, (self.info_box,))
            #     return
            # # 消息盒子，显示1秒 end --------------------------------------------

            # sqlserver_field_name_list = []
            # primary_key = None
            # for i in range(self.table1.rowCount()):
            #     field_name = self.table1.item(i, 0).text()
            #     sqlserver_field_name_list.append(field_name)
            # print(sqlserver_field_name_list)
            self.close_btn.click()

            source_name = 'sqlserver'
            server_name = self.server_input.text()
            port = self.port_input.text()
            username = self.username_input.text()
            password = self.password_input.text()
            db_name = self.db_name_input.text()
            table_name = self.choice_table_comb.currentText()
            field_str = ','.join(["[{0}]".format(item[0]) for item in self.field_common_list])
            # 查询当前表的前十条数据
            sql = """select top 10 {0} from {1}""".format(field_str, table_name)
            print(sql)
            sqlserver_field_content_list = self.sop.select(sql)
            # print(sqlserver_field_content_list)
            # print('sqlserver_field_content_list', sqlserver_field_content_list)

            # 格式：来源名--服务器--端口号--用户名--密码--数据库名--表名--字段名（逗号隔开）
            # field_name_list_str = ','.join(["{0}".format(item[0]) for item in self.field_common_list])
            # the_source_str = '_'.join([source_name, '{0}:{1}'.format(server_name, port), db_name, table_name])
            # self.my_parent_window.daoru_input1.setText(the_source_str)
            self.my_parent_window.daoru_input1.setText('[{0}] - [{1}]'.format(db_name, table_name))

            # primary_key = None
            # for i in range(self.table1.rowCount()):
            #     if self.table1.cellWidget(i, 2).isChecked():
            #         primary_key = '{0}[{1}]'.format(self.table1.item(i, 0).text(), self.table1.item(i, 1).text())
            #         break
            # 来源信息赋值
            qApp.cx_src = {
                # 来源名称
                'name': source_name,
                # 来源独有信息
                'uni': {
                    # 'source_name': source_name,
                    'server_name': server_name,
                    'port': port,
                    'username': username,
                    'password': password,
                    'db_name': db_name,
                    'table_name': table_name,
                    'field_pf_name_list': [item[0] for item in self.field_pf_list],   # 主外键信息
                },
                # 来源普通信息
                'com': {
                    'field_name_list': [item[0] for item in self.field_common_list],
                    'field_content_list': sqlserver_field_content_list,
                }
            }
            # print(qApp.cx_source_sqlserver_dict)
        self.btn2.clicked.connect(field_get)

        # 取消按钮
        self.btn3 = QPushButton(self)
        self.btn3.setText("取消")
        self.btn3.move(720, 490)
        # 点击取消，触发右上角关闭按钮事件
        self.btn3.clicked.connect(self.close_btn.click)

    def open_sqlerver_db_init_window(self):
        '''
        新增sqlserver数据库配置窗口
        :return:
        '''
        self.add_db_window = DbInitWindow(width=900, height=550, my_parent=self)
        self.add_db_window.show()


    def keyPressEvent(self, QKeyEvent):  # 键盘某个键被按下时调用
        '''
        按下 ESC关闭当前页
        :param QKeyEvent:
        :return:
        '''
        # 参数1  控件
        if QKeyEvent.key() == Qt.Key_Escape:  # 判断是否按下了ESC键
            self.close_btn.click()